package sdfi.lee.Newpj.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Session;

import sdfi.lee.DAO.DAO;
import sdfi.lee.Newpj.Entity.NNews;

public class NewsDAO {
	public List<NNews> getNews(int size,int pg,int type)
	{
		List<NNews> list=new ArrayList<NNews>();
		Session session=DAO.openSession();
		Connection con=session.connection();
		try
		{
			con.setAutoCommit(false);
			/*
			 * 	type{3:业界动态 4:区内新闻，2政策法法规
			 * 	业绩动态{3抓取 2手工}
			 * 区内新闻{4抓取 1手工}
			 * 政策法规{2抓取，手工8}；
			 * */
			String sql="";
			if(type==3)/*业绩动态*/
			{
				sql+="select title,dt,type,path,id from (";
				sql+="select newstitle as title,newsdate as dt,0 as type,'path' as path,id as id from news where newstype=2 ";
				sql+="union ";
				sql+="select rolltitle as title,rolldate as dt,1 as type,rollpath as path,id as id from rollnotice where rolltype=3 )";
				sql+="as nnews order by dt desc limit "+(pg-1)*size+","+size;
			}
			else if(type==4)/*中心动态*/
			{
				sql+="select title,dt,type,path,id from (";
				sql+="select newstitle as title,newsdate as dt,0 as type,'path' as path,id as id from news where newstype=1 ";
				sql+="union ";
				sql+="select rolltitle as title,rolldate as dt,1 as type,rollpath as path,id as id from rollnotice where rolltype=4 )";
				sql+="as nnews order by dt desc limit "+(pg-1)*size+","+size;
			}else/*政策法规*/
			{
				sql+="select title,dt,type,path,id from (";
				sql+="select newstitle as title,newsdate as dt,0 as type,'path' as path,id as id from news where newstype=8 ";
				sql+="union ";
				sql+="select rolltitle as title,rolldate as dt,1 as type,rollpath as path,id as id from rollnotice where rolltype=2 )";
				sql+="as nnews order by dt desc limit "+(pg-1)*size+","+size;
			}
			
			ResultSet rs=con.createStatement().executeQuery(sql);
			con.commit();
			while(rs.next())
			{
				NNews news=new NNews();
				news.setId(rs.getInt("id"));
				news.setPath(rs.getString("path"));
				news.setTitle(rs.getString("title"));
				news.setDt(rs.getDate("dt"));
				news.setType(rs.getInt("type"));
				list.add(news);
			}
		}
		catch(Exception ee)
		{
			ee.printStackTrace();
		}
		finally
		{
			DAO.closeSession();
		}
		return list;
	}

	public List<NNews> getNews(String nid,int type)
	{
		List<NNews> list=new ArrayList<NNews>();
		Session session=DAO.openSession();
		Connection con=session.connection();
		try
		{
			con.setAutoCommit(false);
			/*
			 * 	type{3:业界动态 4:区内新闻，2政策法法规
			 * 	业绩动态{3抓取 2手工}
			 * 区内新闻{4抓取 1手工}
			 * 政策法规{2抓取，手工8}；
			 * */
			String sql="";
			if(type==3)/*业绩动态*/
			{
				sql+="select title,dt,type,path,id from (";
				sql+="select rolltitle as title,rolldate as dt,1 as type,rollpath as path,id as id from rollnotice where rolltype=3 )";
				sql+="as nnews order by id asc limit 0,5";
			}
			else if(type==4)/*中心动态*/
			{
				sql+="select title,dt,type,path,id from (";
		
				sql+="select rolltitle as title,rolldate as dt,1 as type,rollpath as path,id as id from rollnotice where rolltype=4 )";
				sql+="as nnews order by id asc limit 0,5";
			}else/*政策法规*/
			{
				sql+="select title,dt,type,path,id from (";
				sql+="select rolltitle as title,rolldate as dt,1 as type,rollpath as path,id as id from rollnotice where rolltype=2 )";
				sql+="as nnews order by dt desc limit 0,5";
			}
			
			ResultSet rs=con.createStatement().executeQuery(sql);
			con.commit();
			while(rs.next())
			{
				NNews news=new NNews();
				news.setId(rs.getInt("id"));
				news.setPath(rs.getString("path"));
				news.setTitle(rs.getString("title"));
				news.setDt(rs.getDate("dt"));
				news.setType(rs.getInt("type"));
				list.add(news);
			}
		}
		catch(Exception ee)
		{
			ee.printStackTrace();
		}
		finally
		{
			DAO.closeSession();
		}
		return list;
	}

	public int getNewsAccount(int type)
	{
		String sql="";
		if(type==3)/*业绩动态*/
		{
			sql+="select count(*) as c from (";
			sql+="select rolltitle as title,rolldate as dt,1 as type,rollpath as path,id as id from rollnotice where rolltype=3 )";
			sql+="as nnews order by id asc";
		}
		else if(type==4)/*中心动态*/
		{
			sql+="select count(*) as c from (";
	
			sql+="select rolltitle as title,rolldate as dt,1 as type,rollpath as path,id as id from rollnotice where rolltype=4 )";
			sql+="as nnews order by id asc";
		}else/*政策法规*/
		{
			sql+="select count(*) as c from (";
			sql+="select rolltitle as title,rolldate as dt,1 as type,rollpath as path,id as id from rollnotice where rolltype=2 )";
			sql+="as nnews order by id asc";
		}
		return DAO.getAccount(sql);
	}
}
